package model;

import java.sql.*;
import java.util.ArrayList;

import connection.DbConnection;
import bean.Poll;
import bean.PollUser;


public class PCmasterImpl {
	
	private static PreparedStatement st;
	private static Connection conn;
	private static ResultSet rs;
		
	public static ArrayList<PollUser> showPollMaster(){
		ArrayList<PollUser> a = new ArrayList<PollUser>();
		
		try{
			rs = null;
			conn = new DbConnection().getConn();
	    	
			st = conn.prepareStatement("select Username, Password, Email, Accessibility, FName, LName, CreateTime FROM PollUser WHERE accessibility = 2");
			rs=st.executeQuery();	
			
	    	while(rs.next()){
	    		PollUser pb = new PollUser();
	    		pb.setUsername(rs.getString(1));
	    		pb.setPassword(rs.getString(2));
	    		pb.setEmail(rs.getString(3));
	    		pb.setAccessibility(rs.getInt(4));
	    		pb.setFirstname(rs.getString(5));
	    		pb.setLastname(rs.getString(6));
	    		pb.setCreateDate(rs.getDate(7));
	    		
	    		a.add(pb);
	    			
	    	}
	    	
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
				
		return a;				
		
	}
	public static ArrayList<PollUser> showPollCreator(){
		ArrayList<PollUser> b = new ArrayList<PollUser>();
		
		try{
			rs = null;
			conn = new DbConnection().getConn();
	    	
			st = conn.prepareStatement("select Username, Password, Email, Accessibility, FName, LName, CreateTime FROM PollUser WHERE accessibility = 3");
			rs=st.executeQuery();	
			
	    	while(rs.next()){
	    		PollUser pb = new PollUser();
	    		pb.setUsername(rs.getString(1));
	    		pb.setPassword(rs.getString(2));
	    		pb.setEmail(rs.getString(3));
	    		pb.setAccessibility(rs.getInt(4));
	    		pb.setFirstname(rs.getString(5));
	    		pb.setLastname(rs.getString(6));
	    		pb.setCreateDate(rs.getDate(7));
	    		
	    		b.add(pb);
	    			
	    	}
	    	
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
				
		return b;				
		
	}
	public static ArrayList<PollUser> showPollUser(){
		ArrayList<PollUser> c = new ArrayList<PollUser>();
		
		try{
			rs = null;
			conn = new DbConnection().getConn();
	    	
			st = conn.prepareStatement("select Username, Password, Email, Accessibility, FName, LName, CreateTime FROM PollUser WHERE accessibility = 1");
			rs=st.executeQuery();	
			
	    	while(rs.next()){
	    		PollUser pb = new PollUser();
	    		pb.setUsername(rs.getString(1));
	    		pb.setPassword(rs.getString(2));
	    		pb.setEmail(rs.getString(3));
	    		pb.setAccessibility(rs.getInt(4));
	    		pb.setFirstname(rs.getString(5));
	    		pb.setLastname(rs.getString(6));
	    		pb.setCreateDate(rs.getDate(7));
	    		
	    		c.add(pb);
	    			
	    	}
	    	
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
				
		return c;				
		
	}
	public static ArrayList<PollUser> showPollAdmin(){
		ArrayList<PollUser> d = new ArrayList<PollUser>();
		
		try{
			rs = null;
			conn = new DbConnection().getConn();
	    	
			st = conn.prepareStatement("select Username, Password, Email, Accessibility, FName, LName, CreateTime, ExpireDate FROM PollUser WHERE accessibility = 4");
			rs=st.executeQuery();	
			
	    	while(rs.next()){
	    		PollUser pb = new PollUser();
	    		pb.setUsername(rs.getString(1));
	    		pb.setPassword(rs.getString(2));
	    		pb.setEmail(rs.getString(3));
	    		pb.setAccessibility(rs.getInt(4));
	    		pb.setFirstname(rs.getString(5));
	    		pb.setLastname(rs.getString(6));
	    		pb.setCreateDate(rs.getDate(7));
	    		pb.setExpireDate(rs.getDate(8));
	    		
	    		d.add(pb);
	    			
	    	}
	    	
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
				
		return d;				
		
	}
	
	public static ArrayList<Poll> showPollInfo(){
		ArrayList<Poll> f = new ArrayList<Poll>();
		
		try{
			rs = null;
			conn = new DbConnection().getConn();
	    	
			st = conn.prepareStatement("select poll.id, poll.version, poll.description, poll.latitude, poll.longitude, poll.createtime, poll.creatorname, poll_ParticipantList.username, attendance from poll, poll_ParticipantList,  (select user_poll.pollid as aid, user_poll.pollversion, count(user_poll.username) as attendance from user_poll group by user_poll.pollid, user_poll.pollversion) where poll.id=aid and poll.id=poll_ParticipantList.pollid order by aid");
			rs=st.executeQuery();	
			
	    	while(rs.next()){
	    		Poll pb = new Poll();
	    		pb.setId(rs.getInt(1));
	    		pb.setVersion(rs.getInt(2));
	    		pb.setDescription(rs.getString(3));
	    		pb.setLatitude(rs.getString(4));
	    		pb.setLongitude(rs.getString(5));
	    		pb.setCreatetime(rs.getDate(6));
	    		pb.setCreator(rs.getString(7));	  
	    		pb.setMaster(rs.getString(8));	
	    		pb.setAttendance(rs.getInt(9));
	    		
	    		f.add(pb);
	    			    			
	    	}
	    	
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
				
		return f;				
		
	}

}
